{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8751bcea-b752-4064-aad2-531bcc2616fe",
   "metadata": {},
   "source": [
    "# Project"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc6f8c81-fb2d-490e-ba3f-f65d33c0b7e0",
   "metadata": {},
   "source": [
    "## Introduction to the Project\n",
    "The S&P 500 (Standard & Poor's 500) is a stock market index that tracks the performance of 500 of the largest publicly traded companies in the United States. It is widely regarded as one of the best representations of the U.S. stock market and economy. Over the long term, the S&P 500 has shown consistent growth, making it a key focus for long-term investors. However, it can also experience significant volatility in the short term.\n",
    "\n",
    "In this project, we will make our first attempt to build a momentum-based strategy to trade the S&P 500 index. At the end of the project, you will have built a program that you can later expand and customise to suit your needs. We will use the python packages numpy, scipy and sqlite3, among others, in this project.\n",
    "\n",
    "Tips: Review the code snippets that we went through during the course. Reorganize them and you are half-way done! Try experimenting with different configurations of the confidence interval, the lookback window and the forecast horizon. Be brave and experiment with different ways of deciding the position size. You may be surprised by your talent!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea94455a-ec49-4b3c-8443-07a250f06501",
   "metadata": {},
   "source": [
    "Re-organize your code from the exercises into a Python program that \n",
    "1. read prices from a database\n",
    "2. calibrate a GBM model on each day when new prices are received.\n",
    "3. forecast the price in e.g. 10 days and\n",
    "   1. calculate the confidence interval of the forecast\n",
    "   2. calculate the expected shortfall from the price forecast\n",
    "4. code your trading signals using the price forecast and the expected shortfall.\n",
    "5. store your postions into the *positions* table after each trade.\n",
    "6. produce a 1-year backtest record from 2020-06-01 to 2021-05-31.\n",
    "\n",
    "**Hint**\n",
    "\n",
    "1. Collect all the code related to the GBM model into a class"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "ee3f26a4-7201-4ffc-89d6-2098fddbcfeb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import sqlite3\n",
    "from contextlib import closing\n",
    "from datetime import datetime\n",
    "\n",
    "import numpy as np\n",
    "from scipy.stats import norm\n",
    "\n",
    "conn = sqlite3.connect(\"SP500.db\")\n",
    "cs = conn.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "1fbcd9e3-631a-448a-af5f-94a2c9e74d85",
   "metadata": {},
   "outputs": [],
   "source": [
    "class GBM:\n",
    "    def __init__(self):\n",
    "        # TODO: Replace the following with your code\n",
    "        self.mu, self.sigma = [np.nan] * 2\n",
    "        \n",
    "    def simulate(self, N, K, Dt, S0):\n",
    "        traj = np.full((N+1, K), np.nan)\n",
    "        # TODO: Your code goes here\n",
    "        return traj\n",
    "\n",
    "    def calibrate(self, trajectory, Dt):\n",
    "        # TODO: Your code goes here\n",
    "        pass\n",
    "        \n",
    "    def forecast(self, latest, T, confidence):\n",
    "        # TODO: Your code goes here\n",
    "        pass\n",
    "        \n",
    "    def expected_shortfall(self, T, confidence):\n",
    "        # TODO: Your code goes here\n",
    "        pass\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "65938171-3635-4466-bd91-893742792a38",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Calibrated: mu = nan, sigma = nan\n"
     ]
    }
   ],
   "source": [
    "# test your code here\n",
    "model = GBM()\n",
    "model.mu = 0.3\n",
    "model.sigma = 0.2\n",
    "simulated = model.simulate(500, 1, 1/250, 100)\n",
    "simulated = simulated[:, 0]\n",
    "\n",
    "model2 = GBM()\n",
    "model2.calibrate(simulated, 1/250)\n",
    "\n",
    "print(F\"Calibrated: mu = {model2.mu}, sigma = {model2.sigma}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64b471cd-b2fb-449c-9c96-386b07307f90",
   "metadata": {},
   "source": [
    "2. Write a function that prepares the database for trading, i.e.\n",
    "   1. load the historical prices into the *prices* table\n",
    "   2. create the *positions* table\n",
    "   3. initialize the *positions* table with the your initial cash reserve. The initial *time_of_trade* can be any date before the earliest possible trading date.\n",
    "\n",
    "    Call this function *prepare*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b83d468e-d4af-48de-a7db-354a8b12cb43",
   "metadata": {},
   "outputs": [],
   "source": [
    "def prepare():\n",
    "    # TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6de3db69",
   "metadata": {},
   "outputs": [],
   "source": [
    "# check whether you have loaded the prices correctly\n",
    "prepare()\n",
    "latest_prices = cs.execute(\"select * from prices order by theday desc limit 10\")\n",
    "for item in latest_prices:\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09f0738b-a96b-4ff5-b2ea-4997f9278d36",
   "metadata": {},
   "source": [
    "3. Write a function that determines the trade size, i.e. how many units of the instrument you would like to own when the date is *which_day* and the price forecast of the instrument is *forecast* and the expected shortfall from the same forecast is *ES*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "96b94a50-5967-4ae5-a1a8-d9dd337f6eee",
   "metadata": {},
   "outputs": [],
   "source": [
    "def position_size(which_day, forecast, ES):\n",
    "    # TODO: Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8679440-f71b-4828-896b-123358c8d4db",
   "metadata": {},
   "source": [
    "4. Write a function that, for a given date, calibrates a GBM model to the data prior to that date and that forecasts the price in 10 days. Call this function *analyse*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1afdaafb-9564-4032-8c69-8e6f7b961f2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def analyse(which_day):\n",
    "    # TODO: Your code goes here    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1373922d-0f6f-4f81-a739-529d2b687ab8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Test the analyse function\n",
    "test_dates = ['2021-05-09', '2021-05-14']\n",
    "positions = [np.nan, np.nan]\n",
    "for i in range(2):\n",
    "    positions[i] = analyse(test_dates[i])\n",
    "    print(F\"{positions[i]} shares advised on {test_dates[i]}.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45307c50-058c-4260-b278-450833e25d03",
   "metadata": {},
   "source": [
    "5. The main loop of the program: Loop over the dates in the backtest period and use the *analyse* function to decide what to do on each day. Call this function *main*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a60d62df-2565-4fe4-bd9c-d9bc508e6444",
   "metadata": {},
   "outputs": [],
   "source": [
    "def main(begin_on):\n",
    "    cs.execute(F\"select theday from prices where theday >= '{begin_on}';\")\n",
    "    days = [d[0] for d in cs.fetchall()]\n",
    "    asset = {\n",
    "        'old': np.nan,\n",
    "        'new': np.nan\n",
    "    };\n",
    "    cash = {\n",
    "        'old': np.nan,\n",
    "        'new': np.nan\n",
    "    };\n",
    "    cs.execute(\"delete from positions where time_of_trade > '2020-01-01';\");\n",
    "    for d in days:\n",
    "        asset['new'] = analyse(d)\n",
    "        cs.execute(F\"\"\"\n",
    "        select quantity, cash from positions\n",
    "        where time_of_trade < '{d}'\n",
    "        order by time_of_trade desc\n",
    "        limit 1;\n",
    "        \"\"\");\n",
    "        asset['old'], cash['old'] = cs.fetchall()[0];\n",
    "        cs.execute(F\"\"\"\n",
    "        select price from prices\n",
    "        where theday <= '{d}'\n",
    "        order by theday desc\n",
    "        limit 1;\n",
    "        \"\"\");\n",
    "        latest = cs.fetchall()[0][0]\n",
    "        trade_size = round(asset['new']) - round(asset['old']);\n",
    "        if trade_size != 0:\n",
    "            cash['new'] = cash['old'] - trade_size * latest;\n",
    "            cs.execute(F\"\"\"\n",
    "            insert into positions values\n",
    "            ('{d}', 'SP500', {round(asset['new'])}, {cash['new']});\n",
    "            \"\"\");\n",
    "        conn.commit();\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a31f680e-2167-4430-969e-9078c198785a",
   "metadata": {},
   "source": [
    "6. Connect to the database and create a *cursor* object associated with the connection. Share the connection and the cursor object across the program so that you don't have to connect to and disconnect from the database in every function of the program."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea240d4b-57f8-41e9-897b-c95ab1016adf",
   "metadata": {},
   "outputs": [],
   "source": [
    "if __name__ == \"__main__\":\n",
    "    with closing(sqlite3.connect(\"SP500.db\")) as conn:\n",
    "        with closing(conn.cursor()) as cs:\n",
    "            prepare()\n",
    "            main('2020-06-01')\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "c2c96d2b-d074-4796-ac96-eb3c4a82d70a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot your track record\n",
    "conn = sqlite3.connect(\"SP500.db\")\n",
    "cs = conn.cursor()\n",
    "\n",
    "day1 = '2020-06-01'\n",
    "day1_dt = datetime.strptime(day1, '%Y-%m-%d')\n",
    "\n",
    "cs.execute(f\"\"\"\n",
    "    select theday, quantity * price + cash as wealth\n",
    "    from positions as PO\n",
    "    join prices as PR\n",
    "    on PO.time_of_trade = (\n",
    "        select time_of_trade from positions\n",
    "        where time_of_trade <= PR.theday\n",
    "        order by time_of_trade desc limit 1\n",
    "    )\n",
    "    where theday >= '{day1}';\n",
    "\"\"\")\n",
    "\n",
    "records = cs.fetchall()\n",
    "\n",
    "def calculate_T(record, day1_dt):\n",
    "    theday, wealth = record\n",
    "    theday_dt = datetime.strptime(theday, '%Y-%m-%d')\n",
    "    T = (theday_dt - day1_dt).days\n",
    "    return (T, wealth)\n",
    "\n",
    "records = [calculate_T(record, day1_dt) for record in records]\n",
    "W = np.asarray(records)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "99a79e6d-32a2-49b2-8b0e-e153ed954546",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "188b36c8-a8a6-4bcb-ac70-c65d1704ef4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "fig = plt.plot(W[:,0], W[:, 1])\n",
    "plt.grid()\n",
    "plt.xlabel(\"Number of days of trading\")\n",
    "plt.ylabel('Total Wealth');"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
